"""
Case Type   : 数据库系统
Case Name   : 1主2备事务同步方式local时备机备份延迟
Create At   : 2021/02/23
Owner       : songjing20
Description :
    1.初始化环境
    2.设置synchronous_commit=local,synchronous_standby_names=''
    3.备节点1使用alter方式设置recovery_min_apply_delay
    4.查询集群同步方式
    5.等待主备一致
    6.创建数据库test,并创建表插入数据
    7.postgres数据库创建表,并插入数据
    8.等待10秒,备机查询,分别查询postgres及test数据库下数据
    9.等待2min,查询备机,分别查询postgres
    10.更新postgres表
    11.设置recovery_min_apply_delay=0
    12.查询备机
    13.更新postgres表
    14.查询备机
    15.清理环境
Expect      :
    1.成功
    2.设置成功
    3.设置成功
    4.两个备机状态为Async
    5.主备同步
    6.创建模式及表并插入数据成功
    7.创建数据成功
    8.备1未同步,备2同步
    9.两个备机均同步
    10.更新成功
    11.设置成功
    12.备机依旧2min后更新
    13.更新成功
    14.备机立即同步
    15.成功
History     :
"""
import unittest
import os
import time
from yat.test import Node
from yat.test import macro
from testcase.utils.Logger import Logger
from testcase.utils.Common import Common
from testcase.utils.Constant import Constant
from testcase.utils.CommonSH import CommonSH

Primary_SH = CommonSH('PrimaryDbUser')


@unittest.skipIf(1 == Primary_SH.get_node_num(),
                 '需主备环境,若为单机环境则不执行')
class RecoveryDelay(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info("----this is setup----")
        self.log.info(f'----{os.path.basename(__file__)} start----')
        self.db_primary_user_node = Node('PrimaryDbUser')
        self.comshpri = CommonSH('PrimaryDbUser')
        self.constant = Constant()
        self.com = Common()
        self.conf_path = os.path.join(
            macro.DB_INSTANCE_PATH, macro.DB_PG_CONFIG_NAME)
        self.tb_name = 'tb_case0003'
        self.db_name = 'db_case0003'
        self.rootnodelist = ['Standby1Root', 'Standby2Root']

    def test_recovery_delay(self):
            text = '----step1:初始化环境 expect:成功----'
            self.log.info(text)
            self.log.info('----1.1备份postgresqlql.conf文件----')
            shell_cmd = f"cp {self.conf_path} {self.conf_path}_testbak;" \
                f"ls -al {self.conf_path}_testbak;"
            self.log.info(shell_cmd)
            result = self.db_primary_user_node.sh(shell_cmd).result()
            self.log.info(result)
            self.assertIn(f'{self.conf_path}_testbak', result, '执行失败' +
                          text)
            self.log.info('----1.2获取节点数目----')
            self.nodelist = ['Standby1DbUser', 'Standby2DbUser']
            result = self.comshpri.get_db_cluster_status('detail')
            self.log.info(result)
            self.node_num = result.count('Standby Normal') + 1
            self.comshsta = []
            self.log.info(self.node_num)
            self.assertEqual(self.node_num, 3, '执行失败' + text)
            for i in range(int(self.node_num) - 1):
                self.comshsta.append(CommonSH(self.nodelist[i]))
            self.log.info('----1.3安装ntp服务----')
            install_cmd = "yum install -y ntp && " \
                          "systemctl enable ntpd && systemctl start ntpd"
            for i in range(int(self.node_num) - 1):
                db_standby_node = Node(node=self.rootnodelist[i])
                result = db_standby_node.sh(install_cmd).result()
                self.log.info(install_cmd)
                self.log.info(result)
            self.log.info('----1.4同步集群时间----')
            current = self.db_primary_user_node.sh(
                "date \"+%m/%d/%Y %H:%M:%S\"").result()
            self.log.info(current)
            datecmd = f'date -s "{current}"'
            for i in range(int(self.node_num) - 1):
                db_standby_node = Node(node=self.rootnodelist[i])
                result = db_standby_node.sh(datecmd).result()
                self.log.info(datecmd)
                self.log.info(result)

            text = '----step2:设置synchronous_commit=local,synchronous_standby' \
                   '_names='' expect:设置成功----'
            self.log.info(text)
            self.result = self.com.show_param('synchronous_standby_names')
            if self.result != '':
                re = self.comshpri.execute_gsguc(
                    "reload", self.constant.GSGUC_SUCCESS_MSG,
                    "synchronous_standby_names=''")
                self.log.info(re)
                self.assertTrue(re, '执行失败' + text)
            self.result1 = self.com.show_param('synchronous_commit')
            if self.result1 != 'local':
                re = self.comshpri.execute_gsguc(
                    'reload', self.constant.GSGUC_SUCCESS_MSG,
                    'synchronous_commit=local')
                self.assertTrue(re, '执行失败' + text)

            text = '----step3:备节点1使用alter方式设置 expect:设置成功----'
            self.log.info(text)
            sql = f"show recovery_min_apply_delay;"
            result = self.comshsta[0].execut_db_sql(sql)
            self.log.info(result)
            sql = f"alter system set recovery_min_apply_delay to '2min' "
            result = self.comshsta[0].execut_db_sql(sql)
            self.log.info(result)
            self.assertIn(self.constant.alter_system_success_msg, result,
                          '执行失败' + text)
            self.log.info('----查询参数----')
            result = self.com.show_param('synchronous_commit;')
            self.log.info(result)
            self.assertIn('local', result, '执行失败' + text)
            result = self.comshpri.execut_db_sql(
                'show recovery_min_apply_delay;')
            self.log.info(result)
            self.result2 = result.strip().splitlines()[-2]
            self.log.info(self.result2)
            self.assertIn('0', result, '执行失败' + text)
            result = self.comshsta[0].execut_db_sql(
                'show recovery_min_apply_delay;')
            self.log.info(result)
            self.assertIn('2min', result, '执行失败' + text)
            result = self.comshpri.execut_db_sql(
                'show synchronous_standby_names;')
            self.log.info(result)
            self.assertEqual(result.strip().splitlines()[-2], ' ', '执行失败' +
                             text)

            text = '----step4:查询集群同步方式 expect:两个备机状态为Async----'
            self.log.info(text)
            sql = "select * from pg_stat_replication;"
            result = self.comshpri.execut_db_sql(sql)
            self.log.info(result)
            self.assertEqual(result.count('Async'), 2, '执行失败' + text)

            text = '----step5:等待主备一致 expect:主备同步----'
            self.log.info(text)
            for i in range(int(self.node_num) - 1):
                result = self.comshsta[i].check_data_consistency()
                self.assertTrue(result, '执行失败' + text)

            text = '----step6:创建数据库test,并创建表插入数据 ' \
                   'expect:创建模式及表并插入数据成功----'
            self.log.info(text)
            time.sleep(5)
            sql = f"drop database if exists {self.db_name};" \
                f"create database {self.db_name};"
            result = self.comshpri.execut_db_sql(sql)
            self.log.info(result)
            self.assertIn(self.constant.CREATE_DATABASE_SUCCESS, result,
                          '执行失败' + text)
            sql = f"drop table if exists {self.tb_name};" \
                f"create table {self.tb_name}(i int, s char(10));" \
                f"insert into {self.tb_name} values(2,'test2');"
            result = self.comshpri.execut_db_sql(sql, dbname=self.db_name)
            self.assertIn(self.constant.TABLE_CREATE_SUCCESS, result,
                          '执行失败' + text)
            self.assertIn(self.constant.INSERT_SUCCESS_MSG, result, '执行失败'
                          + text)

            text = '----step7:创建表,并插入数据 expect:创建数据成功----'
            self.log.info(text)
            sql = f"drop table if exists {self.tb_name};" \
                f"create table {self.tb_name}(i int, s char(10));" \
                f"insert into {self.tb_name} values(1,'test');"
            result = self.comshpri.execut_db_sql(sql)
            self.log.info(result)
            self.assertIn(self.constant.TABLE_CREATE_SUCCESS, result, '执行失败'
                          + text)
            self.assertIn(self.constant.INSERT_SUCCESS_MSG, result, '执行失败'
                          + text)

            text = '----step8:等待10秒,备机查询,分别查询tpcc及test数据库下数据' \
                   'expect:备1未同步,备2同步----'
            self.log.info(text)
            time.sleep(10)
            sql = f"select * from {self.tb_name};"
            for i in range(int(self.node_num) - 1):
                result = self.comshsta[i].execut_db_sql(sql)
                self.log.info(result)
                if 0 == i:
                    self.assertIn(self.constant.NOT_EXIST, result)
                else:
                    self.assertIn('1 | test', result, '执行失败' + text)
                result = self.comshsta[i].execut_db_sql(
                    sql, dbname=self.db_name)
                self.log.info(result)
                if 0 == i:
                    self.assertIn(self.constant.NOT_EXIST, result)
                else:
                    self.assertIn('2 | test2', result, '执行失败' + text)

            text = '----step9:等待2min,查询备机 expect:两个备机均同步----'
            self.log.info(text)
            time.sleep(120)
            sql = f"select * from {self.tb_name};"
            for i in range(int(self.node_num) - 1):
                result = self.comshsta[i].execut_db_sql(sql)
                self.log.info(result)
                self.assertIn('1 | test', result, '执行失败' + text)
                result = self.comshsta[i].execut_db_sql(
                    sql, dbname=self.db_name)
                self.log.info(result)
                self.assertIn('test2', result, '执行失败' + text)

            text = '----step10:更新表 expect:成功----'
            self.log.info(text)
            sql = f"update {self.tb_name} set s='update';"
            result = self.comshpri.execut_db_sql(sql)
            self.log.info(result)
            self.assertIn(self.constant.UPDATE_SUCCESS_MSG, result, '执行失败'
                          + text)

            text = '----step11:使用alter方式设置 expect:设置成功----'
            self.log.info(text)
            sql = f"alter system set recovery_min_apply_delay to '0';" \
                f"select pg_sleep(5);show recovery_min_apply_delay;"
            result = self.comshsta[0].execut_db_sql(sql)
            self.log.info(result)
            self.assertIn('ALTER', result, '执行失败' + text)
            self.assertEqual(str(str(result.split('recovery_min_apply_delay')
                                     [1]).splitlines()[2]).strip(), '0',
                             '执行失败' + text)

            text = '----step12:查询备机 expect:备机依旧2min后更新----'
            self.log.info(text)
            time.sleep(120)
            sql = f"select sysdate;select * from {self.tb_name};"
            for i in range(int(self.node_num) - 1):
                result = self.comshsta[i].execut_db_sql(sql)
                self.log.info(result)
                self.assertIn('1 | update', result, '执行失败' + text)

            text = '----step13:更新表 expect:更新成功----'
            self.log.info(text)
            sql = f"update {self.tb_name} set s='etadpu';"
            result = self.comshpri.execut_db_sql(sql)
            self.log.info(result)
            self.assertIn(self.constant.UPDATE_SUCCESS_MSG, result, '执行失败'
                          + text)

            text = '----step14:查询备机 expect:备机立即同步----'
            self.log.info(text)
            time.sleep(5)
            sql = f"select * from {self.tb_name};"
            for i in range(int(self.node_num) - 1):
                result = self.comshsta[i].execut_db_sql(sql)
                self.log.info(result)
                self.assertIn('1 | etadpu', result, '执行失败' + text)

    def tearDown(self):
        text = '----step15:清理环境 expect:成功----'
        self.log.info(text)
        self.log.info('----删除表------')
        sql = f"drop table if exists {self.tb_name};"
        result_tab = self.comshpri.execut_db_sql(sql)
        self.log.info(result_tab)

        self.log.info('----删除数据库----')
        sql = f"drop database if exists {self.db_name};"
        result_database = self.comshpri.execut_db_sql(sql)
        self.log.info(result_database)

        self.log.info('----还原配置文件----')
        shell_cmd = f"rm -rf {self.conf_path};" \
            f"cp {self.conf_path}_testbak {self.conf_path};" \
            f"rm -rf {self.conf_path}_testbak"
        self.log.info(shell_cmd)
        result_conf = self.db_primary_user_node.sh(shell_cmd).result()
        self.log.info(result_conf)
        shell_cmd1 = f"ls -al {self.conf_path};"
        self.log.info(shell_cmd1)
        shell_cmd2 = f"ls -al {self.conf_path}_testbak;"
        self.log.info(shell_cmd2)
        result_conf1 = self.db_primary_user_node.sh(shell_cmd1).result()
        self.log.info(result_conf1)
        result_conf2 = self.db_primary_user_node.sh(shell_cmd2).result()
        self.log.info(result_conf2)

        self.log.info('----还原synchronous_standby_names,synchronous_commit,'
                      'recovery_min_apply_delay----')
        cmd = f"alter system set synchronous_standby_names to '{self.result}'"
        re1 = self.comshpri.execut_db_sql(cmd)
        self.log.info(re1)
        re2 = self.comshpri.execute_gsguc(
            "reload", self.constant.GSGUC_SUCCESS_MSG,
            f"synchronous_commit='{self.result1}'")
        re3 = self.comshpri.execute_gsguc(
            'reload', self.constant.GSGUC_SUCCESS_MSG,
            f'recovery_min_apply_delay={self.result2}')

        self.assertIn('ALTER', re1, '执行失败' + text)
        self.assertTrue(re2, '执行失败' + text)
        self.assertTrue(re3, '执行失败' + text)
        self.assertNotIn('No such file or directory', result_conf1, '执行失败'
                         + text)
        self.assertIn('No such file or directory', result_conf2, '执行失败' +
                      text)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, result_tab,
                      '执行失败' + text)
        self.assertIn(self.constant.DROP_DATABASE_SUCCESS, result_database,
                      '执行失败' + text)
        self.log.info(f'----{os.path.basename(__file__)} end----')
